package com.hsh.dao.impl;

import com.hsh.bean.Ranking;
import com.hsh.dao.RankingDao;
import com.hsh.util.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RankingDaoImpl extends DruidUtil implements RankingDao {
    @Override
    public List<Ranking> getRankings() {
        List<Ranking> rankings = new ArrayList<>();
        Connection conn = null;
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "select c.cid,c.nickname,count(*) expressNum from courier c,express e where c.cid=e.cid group by c.cid order by expressnum desc;;";
            state = conn.prepareStatement(sql);
            rs = state.executeQuery();
            int i = 1;
            while(rs.next()){
                Ranking ranking = new Ranking();
                ranking.setRid(i++);
                ranking.setNickname(rs.getString("nickname"));
                ranking.setExpressNum(rs.getInt("expressNum"));
                rankings.add(ranking);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

        }
        return rankings;
    }
}
